package com.rankeiot.developer.service;

import com.rankeiot.core.Current;
import com.rankeiot.core.anno.Auth;
import com.rankeiot.core.data.Kv;
import com.rankeiot.core.data.response.Resp;
import com.rankeiot.core.util.CollectionUtil;
import com.rankeiot.core.util.IDUtil;
import com.rankeiot.core.util.StringUtil;
import com.rankeiot.developer.DevelopMenu;
import com.rankeiot.developer.domain.TableFieldMeta;
import com.rankeiot.developer.domain.TableMeta;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.media.Schema;
import jakarta.annotation.PostConstruct;
import jakarta.validation.Valid;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import org.sagacity.sqltoy.SqlToyConstants;
import org.sagacity.sqltoy.config.annotation.Column;
import org.sagacity.sqltoy.config.annotation.Entity;
import org.sagacity.sqltoy.config.annotation.Id;
import org.sagacity.sqltoy.config.model.FieldMeta;
import org.sagacity.sqltoy.config.model.PKStrategy;
import org.sagacity.sqltoy.dao.LightDao;
import org.sagacity.sqltoy.model.Page;
import org.sagacity.sqltoy.model.QueryExecutor;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.cache.annotation.Caching;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.ssssssss.magicapi.datasource.model.DataSourceInfo;
import org.ssssssss.magicapi.datasource.model.MagicDynamicDataSource;
import org.ssssssss.magicapi.datasource.service.DataSourceMagicDynamicRegistry;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Function;

@Component
@RequiredArgsConstructor
public class TableServices {
    private final LightDao dao;
    private final MagicDynamicDataSource magicDynamicDataSource;
    private final DataSourceMagicDynamicRegistry dataSourceMagicDynamicRegistry;

    private Map<String, Function<TableMeta, String>> ddlGenerators = new HashMap<>();

    public void regDDLGenerator(String dbName, Function<TableMeta, String> generator) {
        ddlGenerators.put(dbName, generator);
    }

    @PostConstruct
    public void init() {
        regDDLGenerator("MySQL", this::generateMysqlDDL);
        regDDLGenerator("H2", this::generateH2DDL);
        regDDLGenerator("PostgreSQL", this::generatePGDDL);
    }

    /**
     * 新增表信息
     */
    public void add(TableMeta tableMeta) {
        tableMeta.setId(IDUtil.getUUID());
        tableMeta.setCrateTime(new Date());
        tableMeta.setCreateUser(Current.user().getName());
        setDbType(tableMeta);
        dao.save(tableMeta);
    }

    private void setDbType(TableMeta tm) {
        String dataSource = tm.getDataSource();
        if (dataSource == null) {
            return;
        } else if ("default".equals(dataSource)) {
            try (Connection conn = getConnection(null)) {
                String dbType = conn.getMetaData().getDatabaseProductName();
                tm.setDbType(dbType);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        } else {
            DataSourceInfo dsi = dataSourceMagicDynamicRegistry.mappings().stream().filter(it -> it.getKey().equals(dataSource)).findFirst().get();
            tm.setDbType(driverToDbType(dsi.getDriverClassName()));
        }
    }

    @SneakyThrows
    public Connection getConnection(String dataSourceName) {
        return getDataSource(dataSourceName).getConnection();
    }
    public DataSource getDataSource(String dataSourceName){
        if (dataSourceName == null || "default".equals(dataSourceName)) {
            dataSourceName = "";
        }
        return magicDynamicDataSource.getDataSource(dataSourceName).getDataSource();
    }

    private String driverToDbType(String driverName) {
        if (StringUtil.isEmpty(driverName)) {
            return null;
        }
        if (driverName.indexOf("mysql") > 0) {
            return "MySQL";
        } else if (driverName.indexOf("h2") > 0) {
            return "H2";
        } else if (driverName.indexOf("postgresql") > 0) {
            return "PostgreSQL";
        } else if (driverName.indexOf("oracle") > 0) {
            return "Oracle";
        } else if (driverName.indexOf("sqlserver") > 0) {
            return "SQLServer";
        } else if (driverName.indexOf("db2") > 0) {
            return "DB2";
        }

        return null;
    }

    /**
     * 删除表信息
     */
    @CacheEvict(cacheNames = "table_meta", key = "#id")
    public void delete(String id) {
        dao.delete(new TableMeta(id));
    }

    /**
     * 更新表信息
     */
    @CacheEvict(cacheNames = "table_meta", key = "#tableMeta.id")
    public void save(TableMeta tableMeta) {
        setDbType(tableMeta);
        dao.update(tableMeta);
    }

    /**
     * 获取表信息
     */
    @Cacheable(cacheNames = "table_meta", key = "#id")
    public TableModel detail(String id) {
        TableMeta tableMeta = dao.load(new TableMeta(id));
        if (tableMeta == null) {
            Resp.fail("ID错误");
        }
        return new TableModel(tableMeta);
    }

    /**
     * 检查表模型对应的数据库表是否存在
     *
     * @param id
     * @return
     */
    public boolean checkTable(String id) {
        TableMeta tm = detail(id).getMeta();
        String dataSourceName = tm.getDataSource();
        try (Connection conn = getConnection(dataSourceName)) {
            ResultSet rs = conn.getMetaData().getTables(conn.getCatalog(), conn.getSchema(), tm.getTableName(), new String[]{"TABLE"});
            boolean ret = rs.next();
            rs.close();
            return ret;
        } catch (SQLException e) {
            e.printStackTrace();
            Resp.fail(e.getMessage());
        }
        return false;
    }

    //根据模型生成表，如果已有表则删除旧表
    @Transactional
    public void gentTable(String id, boolean drop) {
        TableMeta tm = detail(id).getMeta();
        DataSource dataSource = magicDynamicDataSource.getDataSource(tm.getDataSource()).getDataSource();
        if (drop) {

            dao.execute().autoCommit(true).dataSource(dataSource).sql("DROP TABLE IF EXISTS " + tm.getTableName()).submit();
        }
        String ddl = tm.getDdl();
        if (StringUtil.isEmpty(ddl)) {
            Function<TableMeta, String> generator = ddlGenerators.get(tm.getDbType());
            if (generator == null) {
                Resp.fail("系统不支持，无法生成数据表");
            }
            ddl = generator.apply(tm);
        }
        dao.execute().autoCommit(true).dataSource(dataSource).sql(ddl).submit();
    }
    public String getSqlDataType(int dataType){
        switch (dataType) {
            case Types.NUMERIC:
                return "NUMERIC";
            case Types.DECIMAL:
                return "DECIMAL";
            case Types.FLOAT:
                return "FLOAT";
            case Types.DOUBLE:
                return "DOUBLE";
            case Types.REAL:
                return "REAL";
            case Types.BIT:
                return "BIT";
            case Types.BOOLEAN:
                return "BOOLEAN";
            case Types.TINYINT:
                return "TINYINT";
            case Types.SMALLINT:
                return "SMALLINT";
            case Types.INTEGER:
                return "INTEGER";
            case Types.BIGINT:
                return "BIGINT";
            case Types.CHAR:
                return "CHAR";
            case Types.VARCHAR:
                return "VARCHAR";
            case Types.LONGVARCHAR:
                return "LONGVARCHAR";
            case Types.CLOB:
                return "CLOB";
            case Types.NCLOB:
                return "NCLOB";
            case Types.BINARY:
                return "BINARY";
            case Types.VARBINARY:
                return "VARBINARY";
            case Types.LONGVARBINARY:
                return "LONGVARBINARY";
            case Types.BLOB:
                return "BLOB";
            case Types.DATE:
                return "DATE";
            case Types.TIME:
                return "TIME";
            case Types.TIMESTAMP:
                return "TIMESTAMP";
            default:
                return "OTHER";
        }
    }
    private String generateMysqlDDL(TableMeta meta) {
        StringBuffer part = new StringBuffer();
        part.append("CREATE TABLE IF NOT EXISTS ");
        part.append(meta.getTableName());
        part.append("(");
        boolean first = true;
        Set<String> pks = new HashSet<>();
        for (TableFieldMeta c : meta.getFieldsMeta()) {

            if (first) {
                first = false;
                part.append("\n\t");
            } else {
                part.append(",\n\t");
            }

            part.append(c.getColumnName());
            part.append(" ");
            String type = c.getNativeType();
            part.append(c.getNativeType());
            if ("date".equals(type) || "timestamp".equals(type) || "int".equals(type)) {

            } else if ("decimal".equals(type)) {
                part.append("(").append(c.getLength()).append(",").append(c.getScale()).append(") ");
            } else {
                part.append("(").append(c.getLength()).append(") ");
            }

            if (c.isPrimaryKey()) {
                //  part.append(" PRIMARY KEY ");
                part.append(" NOT NULL ");
                pks.add("`" + c.getColumnName() + "`");
            } else if (c.isMandatory()) {
                part.append(" NOT NULL ");
            }

            if (PKStrategy.IDENTITY.equals(c.getStrategy())) {
                part.append(" AUTO_INCREMENT ");
            }
            if (c.getDefaultValue() != null && !SqlToyConstants.DEFAULT_NULL.equals(c.getDefaultValue())) {
                part.append(" DEFAULT '").append(c.getDefaultValue()).append("'");
            }

            if (StringUtil.isNotEmpty(c.getMemo())) {
                part.append(" COMMENT '").append(c.getMemo()).append("' ");
            } else if (StringUtil.isNotEmpty(c.getTitle())) {
                part.append(" COMMENT '").append(c.getTitle()).append("' ");
            }
        }
        if (pks.size() > 0) {
            part.append(", PRIMARY KEY (" + String.join(",", pks) + ") USING BTREE");
        }
        part.append("\n) DEFAULT CHARSET=utf8mb4");
        if (StringUtil.isNotEmpty(meta.getMemo())) {
            part.append(" COMMENT ='").append(meta.getMemo()).append("' ");
        }
        part.append(";");
        return part.toString();
    }

    private String generatePGDDL(TableMeta meta) {
        StringBuffer part = new StringBuffer();
        part.append("CREATE TABLE IF NOT EXISTS ");
        part.append(meta.getTableName());
        part.append("(");
        boolean first = true;

        for (TableFieldMeta c : meta.getFieldsMeta()) {

            if (first) {
                first = false;
                part.append("\n\t");
            } else {
                part.append(",\n\t");
            }

            part.append(c.getColumnName());
            part.append(" ");
            String type = c.getNativeType();
            part.append(c.getNativeType());
            if ("date".equals(type) || "timestamp".equals(type) || "int".equals(type)) {

            } else if ("decimal".equals(type)) {
                part.append("(").append(c.getLength()).append(",").append(c.getScale()).append(") ");
            } else {
                part.append("(").append(c.getLength()).append(") ");
            }

            if (c.isPrimaryKey()) {
                part.append(" PRIMARY KEY ");
                part.append(" NOT NULL ");
            } else if (c.isMandatory()) {
                part.append(" NOT NULL ");
            }

            if (PKStrategy.IDENTITY.equals(c.getStrategy())) {
                part.append(" GENERATED ALWAYS AS IDENTITY ");
            }
            if (c.getDefaultValue() != null && !SqlToyConstants.DEFAULT_NULL.equals(c.getDefaultValue())) {
                part.append(" DEFAULT '").append(c.getDefaultValue()).append("'");
            }

//            if (StringUtil.isNotEmpty(c.getMemo())) {
//                part.append(" COMMENT '").append(c.getMemo()).append("' ");
//            } else if(StringUtil.isNotEmpty(c.getTitle())) {
//                part.append(" COMMENT '").append(c.getTitle()).append("' ");
//            }
        }
        part.append("\n);\n");


        for (TableFieldMeta c : meta.getFieldsMeta()) {
            if (StringUtil.isNotEmpty(c.getMemo())) {
                part.append("\nCOMMENT ON COLUMN " + meta.getTableName() + "." + c.getColumnName() + " IS '" + c.getMemo().replaceAll("\n", "\\n") + "';");
            } else if (StringUtil.isNotEmpty(c.getTitle())) {
                part.append("\nCOMMENT ON COLUMN " + meta.getTableName() + "." + c.getColumnName() + " IS '" + c.getTitle() + "';");
            }

        }
        if (StringUtil.isNotEmpty(meta.getMemo())) {
            //COMMENT ON TABLE table_name IS 'comment';
            part.append("\nCOMMENT ON TABLE ")
                    .append(meta.getTableName())
                    .append(" IS '")
                    .append(meta.getMemo().replaceAll("\n", "\\n")).append("'; ");
        } else if (StringUtil.isNotEmpty(meta.getTitle())) {
            part.append("\nCOMMENT ON TABLE ")
                    .append(meta.getTableName())
                    .append(" IS '")
                    .append(meta.getTitle()).append("'; ");
        }
        part.append(";");
        return part.toString();
    }

    private String generateH2DDL(TableMeta meta) {
        StringBuffer part = new StringBuffer();
        part.append("CREATE TABLE IF NOT EXISTS ");
        part.append(meta.getTableName());
        part.append("(");
        boolean first = true;
        Set<String> pks = new HashSet<>();
        for (TableFieldMeta c : meta.getFieldsMeta()) {

            if (first) {
                first = false;
                part.append("\n\t");
            } else {
                part.append(",\n\t");
            }

            part.append(c.getColumnName());
            part.append(" ");
            String type = c.getNativeType();
            part.append(c.getNativeType());
            if ("date".equals(type) || "timestamp".equals(type) || "int".equals(type)) {

            } else if ("decimal".equals(type)) {
                part.append("(").append(c.getLength()).append(",").append(c.getScale()).append(") ");
            } else {
                part.append("(").append(c.getLength()).append(") ");
            }

            if (c.isPrimaryKey()) {
                //  part.append(" PRIMARY KEY ");
                part.append(" NOT NULL ");
                pks.add("`" + c.getColumnName() + "`");
            } else if (c.isMandatory()) {
                part.append(" NOT NULL ");
            }

            if (PKStrategy.IDENTITY.equals(c.getStrategy())) {
                part.append(" AUTO_INCREMENT ");
            }
            if (c.getDefaultValue() != null && !SqlToyConstants.DEFAULT_NULL.equals(c.getDefaultValue())) {
                part.append(" DEFAULT '").append(c.getDefaultValue()).append("'");
            }

            if (StringUtil.isNotEmpty(c.getMemo())) {
                part.append(" COMMENT '").append(c.getMemo()).append("' ");
            } else if (StringUtil.isNotEmpty(c.getTitle())) {
                part.append(" COMMENT '").append(c.getTitle()).append("' ");
            }
        }
        if (pks.size() > 0) {
            part.append(", PRIMARY KEY (" + String.join(",", pks) + ") USING BTREE");
        }
        part.append("\n) DEFAULT CHARSET=utf8mb4");
        if (StringUtil.isNotEmpty(meta.getMemo())) {
            part.append(" COMMENT ='").append(meta.getMemo()).append("' ");
        }
        part.append(";");
        return part.toString();
    }


    @Data
    public class TableModel {
        private TableMeta meta;
        private ViewModel viewModel;
        private DataModel dataModel;

        public TableModel(TableMeta meta) {
            this.meta = meta;
            this.dataModel = new DataModel(meta);
            this.viewModel = new ViewModel(meta);
        }

    }

    private static final Map<String, String> optMap = CollectionUtil.asMap("相等", "=", "大于", ">", "大于等于", ">=", "小于", "<", "小于等于", "<=", "不等", "!=");

    public class DataModel {
        //viewUrl
        private String pageUrl;
        private String dataSourceName;
        private String loadAllSql;

        private String loadSql;

        public DataModel(TableMeta meta) {
            dataSourceName=meta.getDataSource();
            pageUrl = meta.getPageUrl();
            if (pageUrl == null) {
                pageUrl = "common/" + meta.getId() + "/list.vue";
            }
            loadAllSql = meta.getLoadAllSql();
            if (StringUtil.isEmpty(loadAllSql)) {
                StringBuffer sb = new StringBuffer();
                sb.append("SELECT ");
                List<String> selectList = new ArrayList<>();
                List<String> whereList = new ArrayList<>();
                for (TableFieldMeta f : meta.getFieldsMeta()) {
                    if (f.isVisibleInList()||f.isPrimaryKey()) {
                        selectList.add(f.getColumnName());
                    }
                    if (f.isSearchCondition()) {
                        String matcher = matchSearch(f);
                        if (matcher != null) {
                            whereList.add(matcher);
                        }
                    }
                }
                if (selectList.size() == 0) {
                    selectList.add("1");
                }
                sb.append(StringUtil.join(selectList));
                sb.append(" FROM ");
                sb.append(meta.getTableName());
                if (whereList.size() > 0) {
                    sb.append(" WHERE ");
                    sb.append(StringUtil.join(whereList, " "));
                }

                loadAllSql = sb.toString();
            }
            loadSql = meta.getLoadSql();
            if(loadSql==null){
                List<String> whereList=new ArrayList<>();
                for (TableFieldMeta f : meta.getFieldsMeta()) {
                    if(f.isPrimaryKey()){
                        whereList.add(f.getColumnName()+" = :"+f.getField());
                    }
                }
                loadSql="SELECT * FROM "+meta.getTableName()+" WHERE "+StringUtil.join(whereList," AND ");
            }
        }

        private String matchSearch(TableFieldMeta f) {
            String column = f.getColumnName();
            String type = f.getSearchType();
            String filed = f.getField();
            if (StringUtil.isEmpty(type)) {
                return " #[AND " + column + " = :" + filed + "]";
            }
            if ("包含".equals(type)) {
                return " #[AND "+ column +" LIKE CONCAT('%',:" + filed + ",'%')]";
            }
            if ("左边一样".equals(type)) {
                return " #[AND " + column + " LIKE CONCAT(:" + filed + ",'%')]";
            }
            if ("右边一样".equals(type)) {
                return " #[AND " + column + " LIKE CONCAT('%',:" + filed + ")]";
            }
            if ("中间".equals(type)) {
                return " #[AND " + column + " BETWEEN :" + filed + "1 AND " + filed + "2 ]";
            }
            String searchOpt = optMap.get(type);
            if (searchOpt == null) {
                return null;
            }
            // ["相等", "包含", "左边一样", "右边一样", "大于", "大于等于", "小于", "小于等于", "中间", "不等"]
            // optMap
            return " #[AND " + column + " " + searchOpt + " :" + f.getField() + "]";

        }

        public DataSource getDataSource() {
            return magicDynamicDataSource.getDataSource(dataSourceName).getDataSource();
        }

        public Page loadPage(Page page, Map params) {
            QueryExecutor qe=new QueryExecutor(loadAllSql,params);
            qe.dataSource(getDataSource());
            qe.resultType(Map.class);
           return dao.findPageByQuery(page,qe).getPageResult();
        }
        public Map detail(Map params){
            QueryExecutor qe=new QueryExecutor(loadSql,params);
            qe.dataSource(getDataSource());
            qe.resultType(Map.class);
           return (Map)dao.loadByQuery(qe);
        }
    }

    @Data
    public class ViewModel {
        //apis
        //auths
        //filed group
        private String formMode;

        private String listApi;
        private String detailApi;
        private String deleteApi;
        private String saveApi;

        private String authList;
        private String authCreate;
        private String authDelete;
        private String authModify;

        private String roleList;
        private String roleCreate;
        private String roleDelete;
        private String roleModify;

        private List<TableFieldMeta> fieldMetas;
        private List<Kv> viewGroups;

        public ViewModel(TableMeta meta) {
            formMode = meta.getFormModel();
            listApi =  meta.getListApi();
            if(StringUtil.isEmpty(listApi)){
                listApi="model/"+meta.getId()+"/page";
            }

            detailApi=meta.getDetailApi();
            if(StringUtil.isEmpty(detailApi)){
                detailApi="model/"+meta.getId()+"/detail";
            }

            deleteApi=meta.getDeleteApi();
            if(StringUtil.isEmpty(deleteApi)){
                deleteApi="model/"+meta.getId()+"/delete";
            }

            saveApi=meta.getSaveApi();
            if(StringUtil.isEmpty(saveApi)){
                saveApi="model/"+meta.getId()+"/save";
            }

            this.authList=meta.getAuthList();
            this.authCreate=meta.getAuthCreate();
            this.authModify=meta.getAuthModify();
            this.authDelete=meta.getAuthDelete();

            this.roleCreate=meta.getRoleCreate();
            this.roleDelete=meta.getRoleDelete();
            this.roleModify=meta.getRoleModify();
            this.roleList=meta.getRoleList();

            fieldMetas=meta.getFieldsMeta();
            viewGroups = meta.getViewGroups();
        }
    }

}
